﻿using Magicodes.ExporterAndImporter.Core;
using Magicodes.ExporterAndImporter.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace TestConsoleApp1
{
    public class Mysql2excel
    {
        IDbConnection bms_c;
        IDbConnection bms_b;
        IDbConnection bms_m;
        string bms = "Server=localhost;Port=56528;Initial Catalog=bms;Uid=root;Pwd=!(/Qqi+9ka0y;Allow User Variables=true";
        string bms_bill = "Server=localhost;Port=56528;Initial Catalog=bms_bill;Uid=root;Pwd=!(/Qqi+9ka0y;Allow User Variables=true";
        string microserver_data = "Server=localhost;Port=56528;Initial Catalog=microserver_data;Uid=root;Pwd=!(/Qqi+9ka0y;Allow User Variables=true";

        public Mysql2excel()
        {
            bms_c = new MySqlConnector.MySqlConnection(bms);
            bms_b = new MySqlConnector.MySqlConnection(bms_bill);
            bms_m = new MySqlConnector.MySqlConnection(microserver_data);
        }

        Dictionary<int, Project> _projectIdDic = new Dictionary<int, Project>();
        private async Task<List<Project>> GetAll()
        {
            var sql = "select * from projects";
            var rs = await bms_m.QueryAsync<Project>(sql);
            return rs.ToList();
        }
        public async Task<Project> GetProjectAsync(int projectId)
        {
            if (!_projectIdDic.Any())
            {
                var projects = await GetAll();
                _projectIdDic = projects.ToDictionary(x => x.Id, x => x);
            }
            if (_projectIdDic.ContainsKey(projectId))
            {
                return _projectIdDic[projectId];
            }
            return null;

        }
        Dictionary<int, Enterprises> _enterprisesDic = new Dictionary<int, Enterprises>();
        private async Task<List<Enterprises>> GetEnterprisesDicAll()
        {
            var sql = "select * from enterprises";
            var rs = await bms_m.QueryAsync<Enterprises>(sql);
            return rs.ToList();
        }
        public async Task<Enterprises> GetEnterprisesAsync(int id)
        {
            if (!_enterprisesDic.Any())
            {
                var all = await GetEnterprisesDicAll();
                _enterprisesDic = all.ToDictionary(x => x.Id, x => x);
            }
            if (_enterprisesDic.ContainsKey(id))
            {
                return _enterprisesDic[id];
            }
            return null;

        }

        Dictionary<int, Contract> _ContractDic = new Dictionary<int, Contract>();
        private async Task<List<Contract>> GetContractAll()
        {
            var sql = "select * from contracts";
            var rs = await bms_c.QueryAsync<Contract>(sql);
            return rs.ToList();
        }
        public async Task<Contract> GetContractAsync(int id)
        {
            if (!_ContractDic.Any())
            {
                var all = await GetContractAll();
                _ContractDic = all.ToDictionary(x => x.Id, x => x);
            }
            if (_ContractDic.ContainsKey(id))
            {
                return _ContractDic[id];
            }
            return null;

        }

        public async Task Export()
        {
            await this.GetProjectAsync(0);
            await this.GetContractAsync(0);
            await this.GetEnterprisesAsync(0);
            var sql = "select * from billingdetail";
            var reader = await bms_b.ExecuteReaderAsync(sql);
            var rd = reader.GetRowParser<ExportDto>();
            var export = new ExcelExporter();
            var exportDataList = new List<ExportDto>();
            int i = 0;
            while (reader.Read())
            {
                var t = rd(reader);
                var prj = await this.GetProjectAsync(t.ProjectId);
                t.ProjectCode = prj?.Code;
                t.ProjectName = prj?.Name;
                t.ProjectShortName = prj?.ShortName;

                var contract = await this.GetContractAsync(t.ContractId);
                t.EnterpriseId = contract?.EnterpriseId ?? 0;
                t.ContractCode = contract?.Code;
                t.ContractName = contract?.Name;

                if (t.EnterpriseId > 0)
                {
                    var enter = await this.GetEnterprisesAsync(t.EnterpriseId);
                    t.EnterpriseCode = enter?.Code;
                    t.EnterpriseName = enter?.Name;
                    t.SocialCreditCode = enter?.SocialCreditCode;
                }
                i++;
                Console.WriteLine(i);
                exportDataList.Add(t);
            }
            await export.Export("1.xlsx", exportDataList);
        }


    }
    public class Project
    {
        public int Id { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public string ShortName { get; set; }
    }
    public class Contract
    {
        public int Id { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public int EnterpriseId { get; set; }
    }
    public class Enterprises
    {
        public int Id { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public string SocialCreditCode { get; set; }
    }

    [ExcelExporter(Name = "明细")]
    public class ExportDto
    {
        public int ProjectId { get; set; }
        public int ContractId { get; set; }
        public int EnterpriseId { get; set; }

        [ExporterHeader(DisplayName = "项目编码")]
        public string ProjectCode { get; set; }

        [ExporterHeader(DisplayName = "项目名称")]
        public string ProjectName { get; set; }

        [ExporterHeader(DisplayName = "项目简称")]
        public string ProjectShortName { get; set; }

        [ExporterHeader(DisplayName = "协议编码")]
        public string ContractCode { get; set; }

        [ExporterHeader(DisplayName = "协议名称")]
        public string ContractName { get; set; }

        [ExporterHeader(DisplayName = "客户编码")]
        public string EnterpriseCode { get; set; }

        [ExporterHeader(DisplayName = "客户名称")]
        public string EnterpriseName { get; set; }

        [ExporterHeader(DisplayName = "社会信用编码")]
        public string SocialCreditCode { get; set; }

        [ExporterHeader(DisplayName = "明细编码")]
        public string Code { get; set; }

        [ExporterHeader(DisplayName = "台账编码")]
        public string StandingBookCode { get; set; }

        [ExporterHeader(DisplayName = "业务单号")]
        public string BusinessNumber { get; set; }

        [ExporterHeader(DisplayName = "服务项编码")]
        public string ServiceItemCode { get; set; }

        [ExporterHeader(DisplayName = "结算项编码")]
        public string SettlementItemCode { get; set; }

        [ExporterHeader(DisplayName = "作业结束时间")]
        public string GeneratedOn { get; set; }

        [ExporterHeader(DisplayName = "创建时间")]
        public string CreatedOn { get; set; }

        [ExporterHeader(DisplayName = "金额")]
        public string Amount { get; set; }

        [ExporterHeader(DisplayName = "税率")]
        public string Tax { get; set; }
    }
}
